package com.zerosky.crm.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.zerosky.crm.po.Branch;
import com.zerosky.crm.po.Department;
import com.zerosky.crm.po.Employee;

public class ExcelUtil {
	public static String NO_DEFINE = "no_define";// 未定义的字段
	public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式
	public static int DEFAULT_COLOUMN_WIDTH = 17;

	/**
	 * 导出Excel 97(.xls)格式 ，少量数据
	 * 
	 * @param title
	 *            标题行
	 * @param headMap
	 *            属性-列名
	 * @param jsonArray
	 *            数据集
	 * @param datePattern
	 *            日期格式，null则用默认日期格式
	 * @param colWidth
	 *            列宽 默认 至少17个字节
	 * @param out
	 *            输出流
	 */
	public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern,
			int colWidth, OutputStream out) {
		if (datePattern == null)
			datePattern = DEFAULT_DATE_PATTERN;
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		workbook.createInformationProperties();
		SummaryInformation si = workbook.getSummaryInformation();
		si.setApplicationName("导出程序"); // 填加xls文件创建程序信息
		si.setLastAuthor("最后保存者信息"); // 填加xls文件最后保存者信息
		si.setCreateDateTime(new Date());
		// 表头样式
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBoldweight((short) 700);
		titleStyle.setFont(titleFont);
		// 列头样式
		HSSFCellStyle headerStyle = workbook.createCellStyle();
		// headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints((short) 12);
		headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		headerStyle.setFont(headerFont);
		// 单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		HSSFFont cellFont = workbook.createFont();
		cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		cellStyle.setFont(cellFont);
		// 生成一个(带标题)表格
		HSSFSheet sheet = workbook.createSheet();
		// 设置列宽
		int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;// 至少字节数
		int[] arrColWidth = new int[headMap.size()];
		// 产生表格标题行,以及设置列宽
		String[] properties = new String[headMap.size()];
		String[] headers = new String[headMap.size()];
		int ii = 0;
		for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
			String fieldName = iter.next();

			properties[ii] = fieldName;
			headers[ii] = headMap.get(fieldName);

			int bytes = fieldName.getBytes().length;
			arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
			sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
			ii++;
		}
		// 遍历集合数据，产生数据行
		int rowIndex = 0;
		for (Object obj : jsonArray) {
			if (rowIndex == 65535 || rowIndex == 0) {
				if (rowIndex != 0)
					sheet = workbook.createSheet();// 如果数据超过了，则在第二页显示

				HSSFRow titleRow = sheet.createRow(0);// 表头 rowIndex=0
				titleRow.createCell(0).setCellValue(title);
				titleRow.getCell(0).setCellStyle(titleStyle);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

				HSSFRow headerRow = sheet.createRow(1); // 列头 rowIndex =1
				for (int i = 0; i < headers.length; i++) {
					headerRow.createCell(i).setCellValue(headers[i]);
					headerRow.getCell(i).setCellStyle(headerStyle);

				}
				rowIndex = 2;// 数据内容从 rowIndex=2开始
			}
			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
			HSSFRow dataRow = sheet.createRow(rowIndex);
			for (int i = 0; i < properties.length; i++) {
				HSSFCell newCell = dataRow.createCell(i);

				Object o = jo.get(properties[i]);
				String cellValue = "";
				if (o == null)
					cellValue = "";
				else if (o instanceof Date)
					cellValue = new SimpleDateFormat(datePattern).format(o);
				else
					cellValue = o.toString();

				newCell.setCellValue(cellValue);
				newCell.setCellStyle(cellStyle);
			}
			rowIndex++;
		}
		try {
			workbook.write(out);
			// workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出Excel 2007 OOXML (.xlsx)格式
	 * 
	 * @param title
	 *            标题行
	 * @param headMap
	 *            属性-列头
	 * @param jsonArray
	 *            数据集
	 * @param datePattern
	 *            日期格式，传null值则默认 年月日
	 * @param colWidth
	 *            列宽 默认 至少17个字节
	 * @param out
	 *            输出流
	 */
	// Web 导出excel
	public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja,
			HttpServletResponse response) {
		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			ExcelUtil.exportExcel(title, headMap, ja, null, 0, os);
			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);
			// 设置response参数，可以打开下载页面
			response.reset();

			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
			response.setHeader("Content-Disposition",
					"attachment;filename=" + new String(
							(title + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls").getBytes(),
							"iso-8859-1"));
			response.setContentLength(content.length);
			ServletOutputStream outputStream = response.getOutputStream();
			BufferedInputStream bis = new BufferedInputStream(is);
			BufferedOutputStream bos = new BufferedOutputStream(outputStream);
			byte[] buff = new byte[8192];
			int bytesRead;
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);

			}
			bis.close();
			bos.close();
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导入员工信息Excel文件
	 * 
	 * @return 数据集合
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	@SuppressWarnings("unused")
	public static List<Employee> readExcel(String url, List<Department> deptList, List<Branch> branchList,
			List<Branch> teamList, String name) throws FileNotFoundException, IOException {
		List<Employee> list = new ArrayList<Employee>();
		XSSFWorkbook workbook = null;
		// 1.得到Excel常用对象
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(url));
		// 2.得到Excel工作簿对象
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		// 3.得到Excel工作表对象
		HSSFSheet sheet = wb.getSheetAt(0);
		// 总行数
		int trLength = sheet.getPhysicalNumberOfRows();
		// new一个创建时间
		Date createDate = new Date();
		List<String> errormsg = new ArrayList<String>();
		// 利用foreach循环 遍历Excel中的所有行
		for (int i = 1; i <= trLength; i++) {
			// 将单元格中的内容存入集合
			Employee employee = new Employee();
			String strId = UUID.randomUUID().toString();
			employee.setEmployeeId(strId);// UUID
			employee.setRid(70);// 默认角色
			employee.setPassword(MD5Util.encrypt("123456"));
			employee.setCreateDate(new Date());// 创建时间
			employee.setCreatePeople(name);// 创建人
			employee.setQuarters("0");// 默认岗位无
			employee.setStatus("0");// 默认有效
			// 4.得到Excel工作表的行
			HSSFRow row1 = sheet.getRow(i);
			if (row1 != null) {
				// 总列数
				int tdLength = row1.getLastCellNum();
				if (tdLength >= 1) {
					for (int j = 0; j < tdLength; j++) {
						if (row1.getCell(j) != null && !row1.getCell(j).equals("")) {
							row1.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
							if (row1 == null) {
								continue;
							}
							if (j == 0) {
								HSSFCell cell = row1.getCell(0);
								if (cell == null) {
									continue;
								}
								employee.setName(cell.getStringCellValue());
							} else if (j == 1) {
								HSSFCell cell = row1.getCell(1);
								if (cell != null) {

									switch (cell.getCellType()) {
									case HSSFCell.CELL_TYPE_NUMERIC: // 数字
										NumberFormat nf = NumberFormat.getInstance();
										String s = nf.format(cell.getNumericCellValue());
										// 这种方法对于自动加".0"的数字可直接解决
										// 但如果是科学计数法的数字就转换成了带逗号的，例如：12345678912345的科学计数法是1.23457E+13，经过这个格式化后就变成了字符串“12,345,678,912,345”，这也并不是想要的结果，所以要将逗号去掉
										if (s.indexOf(",") >= 0) {
											s = s.replace(",", "");
										}
										// 此时得到的结果s就是从Excel单元格中填写的数据
										employee.setUserCard(s);
										break;
									case HSSFCell.CELL_TYPE_STRING: // 字符串
										employee.setUserCard(cell.getStringCellValue() + "");
										break;
									}
								}

							} else if (j == 2) {
								HSSFCell cell = row1.getCell(2);
								if (cell == null) {
									continue;
								}
								switch (cell.getCellType()) {
								case HSSFCell.CELL_TYPE_NUMERIC: // 数字
									NumberFormat nf = NumberFormat.getInstance();
									String s = nf.format(cell.getNumericCellValue());
									// 这种方法对于自动加".0"的数字可直接解决
									// 但如果是科学计数法的数字就转换成了带逗号的，例如：12345678912345的科学计数法是1.23457E+13，经过这个格式化后就变成了字符串“12,345,678,912,345”，这也并不是想要的结果，所以要将逗号去掉
									if (s.indexOf(",") >= 0) {
										s = s.replace(",", "");
									}
									// 此时得到的结果s就是从Excel单元格中填写的数据
									employee.setPhoneNumber(s);
									break;
								case HSSFCell.CELL_TYPE_STRING: // 字符串
									employee.setPhoneNumber(cell.getStringCellValue() + "");
									break;
								}
							} else if (j == 3) {
								HSSFCell cell = row1.getCell(3);
								if (cell != null) {
									employee.setSex(cell.getStringCellValue());
								}

							} else if (j == 4) {
								HSSFCell cell = row1.getCell(4);
								if (cell != null) {
									employee.setPosition(cell.getStringCellValue());
								}

							} else if (j == 5) {
								HSSFCell cell = row1.getCell(5);
								if (cell == null) {
									continue;
								}
								HSSFCell cell2 = row1.getCell(6);
								if (cell2 == null) {
									continue;
								}
								for (Department dept : deptList) {
									if (dept.getDeptName().equals(cell.getStringCellValue())
											&& dept.getRemake().equals(cell2.getStringCellValue())) {
										employee.setDeptId(dept.getDeptId());
									}
								}
							} else if (j == 7) {
								HSSFCell cell = row1.getCell(7);
								for (Branch branch : branchList) {
									if (cell != null && branch.getDept() != null && employee.getDeptId() != null
											&& branch.getBranchName().equals(cell.getStringCellValue())
											&& branch.getDept().getDeptId().equals(employee.getDeptId())) {
										employee.setBranchId(branch.getBranchId());
									}
								}
							} else if (j == 8) {
								HSSFCell cell = row1.getCell(8);
								for (Branch team : teamList) {
									if (cell != null && employee.getBranchId() != null
											&& team.getBranchName().equals(cell.getStringCellValue())
											&& team.getPid().equals(employee.getBranchId())) {
										employee.setTeamId(team.getBranchId());
									}
								}
							}
						}
					}
					if(employee.getName()!=null && employee.getName()!=""){
						list.add(employee);
					}
				}
			}else{
				break;
			}
		}
		return list;
	}
}
